RDS for PostgreSQLのロケールをCにする

はじめに

こんにちは、望月です。

RDS for PostgreSQLへの移行を行い、ひさしぶりにPostgreSQLを触ることになりました。 マネージドサービス特有の留意ポイントがあったため、今後RDS for PostgreSQLへの移行を考えてる人の役に立てばと思い、共有致します。

PostgreSQLのロケール設定

PostgreSQLを使う上で、ロケールを無効にする、 --no-locale するといったことをあるかと思います。 これはロケールを C で設定するという意味であり、この設定は文字列のソート順序に影響があります。

ロケールについては、下記サイトに詳しいことが書かれているので参考にしてください。

ロケール(国際化と地域化)

このロケール設定ですが、昔(8.3以前)はinitdbコマンド実行時にのみロケールの指定ができるということで、 最初に initdb --no-locale を実行するというのが多かったかと思います。

しかし、RDS for PostgreSQLでは initdb --no-locale ができないため、ロケールを無効にするにはcreatedbコマンド実行時にロケールを指定する必要があります。

ということで、やってみました。

ロケールを無効にしてみる

--no-locale した場合

まずは、CentOS7にPostgreSQL9.2.24をインストールし --no-locale した場合のロケールを確認してみます。

$ initdb -D /var/lib/pgsql/data --no-locale
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

postgres -D /var/lib/pgsql/data
or
pg_ctl -D /var/lib/pgsql/data -l logfile start

みごとにすべてのロケールが C になっています。

postgres=# SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
name | setting | context
-------------+---------+-----------
lc_collate | C | internal
lc_ctype | C | internal
lc_messages | C | superuser
lc_monetary | C | user
lc_numeric | C | user
lc_time | C | user
(6 rows)

RDS for PostgreSQLの場合

RDS for PostgreSQLのロケールを確認してみます。

まずはパラメータグループがデフォルトの状態を確認してみます。

postgres=> SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
name | setting | context
-------------+-------------+-----------
lc_collate | en_US.UTF-8 | internal
lc_ctype | en_US.UTF-8 | internal
lc_messages | | superuser
lc_monetary | C | user
lc_numeric | C | user
lc_time | C | user
(6 rows)

lc_collate、lc_ctype、lc_messagesが C になっていません。

パラメータグループで変更できる項目が、lc_messages、lc_monetary、lc_numeric、lc_timeとなっているため、 すべて C へ変更した状態で、再度確認してみます。

postgres=> SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
name | setting | context
-------------+-------------+-----------
lc_collate | en_US.UTF-8 | internal
lc_ctype | en_US.UTF-8 | internal
lc_messages | C | superuser
lc_monetary | C | user
lc_numeric | C | user
lc_time | C | user
(6 rows)

lc_collate、lc_ctype以外は C になりました。 この、2つがRDS for PostgreSQLでは変更できず、createdbコマンドでロケールの指定を行う必要があります。

では、createdbコマンドを使い、DB(testdb)を作成し、ロケールを指定してみます。

postgres=> CREATE DATABASE testdb LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbname | testuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | testuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +
| | | | | rdsadmin=CTc/rdsadmin
template1 | testuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/testuser +
| | | | | testuser=CTc/testuser
testdb | testuser | UTF8 | C | C |
(6 rows)

これで、lc_collate、lc_ctypeが C になり、すべてが C となりました。

注意点として、インスタンス作成時に合わせ作成したDB(dbname)はlc_collate、lc_ctypeが C ではないため 使用せず、新規にDBを作成する必要があります。

最後に

ロケールが違うと文字列のソート順序に影響があり、想定通りの動作にならなくなります。

移行の際は、事前にロケール設定を確認の上、進めていきましょう。